<?php

namespace app\models;

use yii;
use yii\db\Query;

class Invoice
{
    private $table;

    public function __construct()
    {
        $this->table = yii::$app->db->tablePrefix . 'invoice';
    }

    public function one($where = [])
    {
        $db = new Query();
        $db->select('*')->from($this->table);

        if (!empty($where['id'])) $db->andWhere(['id' => $where['id']]);
        if (!empty($where['invoice_id'])) $db->andWhere(['invoice_id' => $where['invoice_id']]);
        return $db->limit(1)->one();
    }

    public function check_invoice($invoice)
    {
        $db = new Query();
        return $db->select('id')
            ->from(yii::$app->db->tablePrefix . 'pi')
            ->where(['invoice' => $invoice])
            ->one();
    }

    public function delInvoice($id)
    {
        $db = new Query();
        return $db->createCommand()->delete($this->table, ['id' => $id])->execute();
    }

    public function add_invoice($data)
    {
        return yii::$app->db->createCommand()->insert($this->table, $data)->execute();
    }

    /**获取一条记录
     * @param $data
     * @return array|bool
     */
    public function getRecordByParam($data)
    {
        $db = new Query();
        return $db->select('*')
            ->from($this->table)
            ->where([$data['param'] => $data['value'], 'pid' => 0])
            ->limit(1)->one();
    }

    public function get_invoice_list_by_job_number($p, $s, $where = [])
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        //管理员id为空
        if (!empty($where['id'])) $db->andWhere(['id' => $where['id']]);
        if (!empty($where['job_number'])) {
            $db->andWhere(['job_number' => $where['job_number']]);
        }
        if (!empty($where['invoice'])) {
            $db->andWhere(['invoice_id' => $where['invoice']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andWhere(['like', 'customer_info', $where['customer_name']]);
        }
        if (!empty($where['show_invoice'])) {
            $db->andWhere(['invoice_id' => '']);
        }
        $db->andWhere(['pid' => 0]);
        return $db->offset(($p - 1) * $s)
            ->limit($s)
            ->orderBy(['date' => SORT_DESC,'id'=>SORT_DESC])
            ->all();
    }

    /**invoice的size
     * @param array $where
     * @return int
     */
    public function get_invoice_size_by_job_number($where = [])
    {
        $db = new Query();
        $db->select('count(*) as num')->from($this->table);
        //管理员查看全部
        if (!empty($where['id'])) $db->andWhere(['id' => $where['id']]);
        if (!empty($where['job_number'])) {
            $db->andWhere(['job_number' => $where['job_number']]);
        }

        if (!empty($where['invoice'])) {
            $db->andWhere(['invoice_id' => $where['invoice']]);
        }

        if (!empty($where['customer_name'])) {
            $db->andWhere(['like', 'customer_info', $where['customer_name']]);
        }

        if (!empty($where['show_invoice'])) {
            $db->andWhere(['invoice_id' => '']);
        }

        $db->andWhere(['pid' => 0]);
        $res = $db->one();

        return $res ? $res['num'] : 0;
    }

    /**更新操作
     * @param $data
     * @param $id
     * @return int
     */
    public function updateInvoice($data, $id)
    {
        return yii::$app->db->createCommand()->update($this->table, $data, ['id' => $id])->execute();
    }

    public function get_invoice_by_id($id)
    {
        $db = new Query();
        return $db->select('a.*,b.en_name,b.email')
            ->from(yii::$app->db->tablePrefix . "invoice as a")
            ->join('LEFT JOIN', yii::$app->db->tablePrefix . "staff as b", 'a.job_number=b.job_number')
            ->where(['a.id' => $id])
            ->limit(1)
            ->one();
    }

    public function edit_invoice_by_invoice($invoice, $data)
    {
        return yii::$app->db->createCommand()
            ->update($this->table, $data, ['invoice' => $invoice])
            ->execute();
    }

    public function get_invoice_by_pi($where = [])
    {
        $db = new Query();
        $db->select('id,pi_id')->from($this->table);
        if (!empty($where['pi_id'])) $db->andWhere(['in', 'pi_id', $where['pi_id']]);
        return $db->all();
    }

    //invoice所有关联信息
    public function get_invoice_all_info($p, $s, $where = [])
    {
        $query = new Query();
        $query->select('invocie_num,service_name')->from(yii::$app->db->tablePrefix . 'bank_bill')
            ->groupBy('invocie_num');
        $db = new Query();
        $db->select('a.*,b.service_name as purchase_name')
            ->from(yii::$app->db->tablePrefix . "invoice_new as a")
            ->join('left join', ['b' => $query], 'a.invoice=b.invocie_num')
            ->where(['!=', 'invoice', '']);
        if ($where['job_number']) {
            $db->andWhere(['saleman_num' => $where['job_number']]);
        }
        if ($where['begin_date']) {
            $db->andWhere(['>=', 'date', $where['begin_date']]);
        }
        if ($where['end_date']) {
            $db->andWhere(['<=', 'date', $where['end_date']]);
        }

        if ($where['customer_name']) {
            $db->andWhere(['like', 'customer_name', $where['customer_name']]);
        }
        if ($where['invoice']) {
            $db->andWhere(['invoice' => $where['invoice']]);
        }
        if ($where['invoice_status']) {
            $db->andWhere(['invoice_status' => $where['invoice_status']]);
        }
        if (!empty($where['order_status'])) {
            $db->andWhere(['order_status' => $where['order_status']]);
        }
        if (!empty($where['purchase'])) {
            $db->andWhere(['purchase' => $where['purchase']]);
        }
        if (!empty($where['recommend'])) {
            $db->andWhere(['recommend' => $where['recommend']]);
        }
        if (!empty($where['is_jiesuan'])) $db->andWhere(['is_jiesuan' => $where['is_jiesuan']]);
        if ($where['confirm_begin_date']) {
            $db->andWhere(['>=', 'confirm_date', $where['confirm_begin_date']]);
        }
        if ($where['confirm_end_date']) {
            $db->andWhere(['<=', 'confirm_date', $where['confirm_end_date']]);
        }
        $list = $db->offset(($p - 1) * $s)
            ->limit($s)
            ->orderBy(['date' => SORT_DESC])
            ->all();
        $invoice_arr = [];
        foreach ($list as $k => $v) {
            $invoice_arr[] = $v['invoice'];
        }

        $query = new Query();//收入信息
        $query = $query->select(["date_format(date,'%Y-%m') as date", "invocie_num,country,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee"])
            ->from(yii::$app->db->tablePrefix . "income")
            ->where(['in', 'invocie_num', $invoice_arr])
            ->groupBy(['invocie_num', 'date']);

        $db = new Query();
        $income_list = $db->select(["GROUP_CONCAT(date order by in_come desc) as info", "invocie_num,country,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee"])
            ->from(["a" => $query])
            ->groupBy("invocie_num")
            ->all();
        foreach ($income_list as $k => $v) {
            $info = $v['info'];
            $date = explode(',', $info);
            $income_list[$k]['date'] = $date[0];
        }
        return ['list' => $list, 'income' => $income_list];
    }

    public function get_invoice_new_by_id($id, $m)
    {
        $db = new Query();
        $list = $db->select(['*', 'date_format(date,"%Y-%m") as m'])
            ->from(yii::$app->db->tablePrefix . "invoice_new")
            ->where(['saleman_num' => $id, 'date_format(date,"%Y-%m")' => $m])
            ->orderBy(['date' => SORT_DESC])
            ->all();
        $invoice_arr = [];
        foreach ($list as $k => $v) {
            $invoice_arr[] = $v['invoice'];
        }

        $db = new Query();//收入信息
        $income_list = $db->select('invocie_num,country,sum(in_come) as in_come,sum(total_fee) as total_fee,sum(fee) as fee')
            ->from(yii::$app->db->tablePrefix . "income")
            ->where(['in', 'invocie_num', $invoice_arr])
            ->groupBy(['invocie_num'])
            ->all();

        return ['list' => $list, 'income' => $income_list];
    }

    public function get_invoice_new_size($where = [])
    {
        $db = new Query();
        $db->select('count(*) as num')
            ->from(yii::$app->db->tablePrefix . "invoice_new")
            ->where(['!=', 'invoice', '']);
        if ($where['job_number']) {
            $db->andWhere(['saleman_num' => $where['job_number']]);
        }
        if ($where['begin_date']) {
            $db->andWhere(['>=', 'date', $where['begin_date']]);
        }
        if ($where['end_date']) {
            $db->andWhere(['<=', 'date', $where['end_date']]);
        }

        if ($where['customer_name']) {
            $db->andWhere(['like', 'customer_name', $where['customer_name']]);
        }
        if ($where['invoice']) {
            $db->andWhere(['invoice' => $where['invoice']]);
        }
        if ($where['invoice_status']) {
            $db->andWhere(['invoice_status' => $where['invoice_status']]);
        }
        if (!empty($where['order_status'])) {
            $db->andWhere(['order_status' => $where['order_status']]);
        }
        if (!empty($where['purchase'])) {
            $db->andWhere(['purchase' => $where['purchase']]);
        }
        if (!empty($where['recommend'])) {
            $db->andWhere(['recommend' => $where['recommend']]);
        }
        if (!empty($where['is_jiesuan'])) $db->andWhere(['is_jiesuan' => $where['is_jiesuan']]);
        if ($where['confirm_begin_date']) {
            $db->andWhere(['>=', 'confirm_date', $where['confirm_begin_date']]);
        }
        if ($where['confirm_end_date']) {
            $db->andWhere(['<=', 'confirm_date', $where['confirm_end_date']]);
        }
        return $db->limit(1)
            ->one();
    }

    //invoice统计
    public function invoice_count($year)
    {
        $sql = "SELECT date_format(date,'%Y-%m') as m,count(invoice) as num,COUNT( DISTINCT customer_num ) as customer_num,saleman as name ,SUM(money) as money,saleman_num as service_name
            FROM (
                SELECT a.date,a.invoice,a.saleman,a.saleman_num,b.money,a.customer_num
                FROM `jingsourcing_invoice_new` as a
                LEFT JOIN
                (
                    SELECT SUM( in_come + total_fee + fee ) AS money,invocie_num
                    FROM  `jingsourcing_income` 
                    WHERE  `invocie_num` !=  '' AND DATE_FORMAT(date,'%Y')=" . $year .
            " GROUP BY `invocie_num`
                ) AS b
                ON a.invoice=b.invocie_num
                WHERE DATE_FORMAT(date,'%Y')=" . $year . " AND invoice!=''
            ) AS a 
            GROUP BY m,saleman_num
            ORDER BY saleman_num ASC,m DESC";
        return yii::$app->db->createCommand($sql)->queryAll();
    }


    //获取所有有invocie的pi_id
    public function get_all_pi_id()
    {
        $db = new Query();
        $list = $db->select('pi_id')
            ->from($this->table)
            ->all();

        $pi_arr = [];
        if ($list) {
            foreach ($list as $v) {
                $pi_arr[] = $v['pi_id'];
            }
        }

        return $pi_arr;
    }


    //生成一个invoice编号
    public function generator_invoice()
    {
        $db = new Query();
        $date = date('Y-m-d');
        $info = $db->select('count(distinct invoice_id) as num')
            ->from($this->table)
            ->where(['date' => $date])
            ->one();

        $count = $info ? $info['num'] : 0;

        return 'IN' . date('ymd') . str_pad($count + 1, 3, 0, STR_PAD_LEFT);
    }

    //获取历史版本列表
    public function get_older_version($arr)
    {
        $db = new Query();
        $db->select('id,create_at,invoice_id,pid')->from($this->table);
        $db->andWhere(['!=', 'pid', 0]);
        $list = $db->andWhere(['in', 'pid', $arr])->all();
        $output = [];
        foreach ($list as $v) {
            $output[$v['pid']][] = $v;
        }

        return $output;
    }

    /**获取历史记录
     * @param $arr
     * @return array
     */
    public function getOlderIn($arr)
    {
        $db = new Query();
        $db->select('id,create_at,invoice_id,pid')->from($this->table);
        $db->andWhere(['!=', 'pid', 0]);
        $db->andWhere(['in', 'pid', $arr])->all();
        $list = $db->orderBy(['create_at' => SORT_DESC])->all();
        return $list;
    }

    //复制一条记录
    public function clone_invoice_by_id($id)
    {
        $db = new Query();
        $info = $db->select('*')
            ->from($this->table)
            ->where(['id' => $id])
            ->one();

        return $info;
    }

    public function get_all_invoice_by_job_number($id)
    {
        $db = new Query();
        $list = $db->select('distinct(invoice_id)')
            ->from($this->table)
            ->where(['job_number' => $id])
            ->all();

        $invoice = [];
        foreach ($list as $v) :
            $invoice[] = $v['invoice_id'];
        endforeach;

        return $invoice;
    }
}
